-----------------------------------------------------Agregar Banco-----------------------------------------------------
DROP PROCEDURE AgregarBanco;
GO

CREATE PROCEDURE AgregarBanco(@nombre varchar(30))
AS
BEGIN
	BEGIN TRANSACTION
	INSERT INTO Banco(nombre_banco) VALUES(@nombre);
	IF @@ERROR != 0
		BEGIN
			ROLLBACK TRANSACTION
			RETURN
		END
	COMMIT TRANSACTION
END;
GO

-----------------------------------------------------EliminarBanco-----------------------------------------------------
DROP PROCEDURE EliminarBanco;
GO

CREATE PROCEDURE EliminarBanco(@id numeric(11))
AS
BEGIN
	BEGIN TRANSACTION
	DELETE FROM Banco WHERE id_banco=@id;
	IF @@ERROR != 0
		BEGIN
			ROLLBACK TRANSACTION
			RETURN
		END
	COMMIT TRANSACTION
END;
GO

-----------------------------------------------------Actualizar Banco-----------------------------------------------------
DROP PROCEDURE ActualizarBanco;
GO

CREATE PROCEDURE ActualizarBanco(@nombre varchar(30),@id numeric(11))
AS
BEGIN
	BEGIN TRANSACTION
	UPDATE Banco SET nombre_banco=@nombre WHERE id_banco=@id;
	IF @@ERROR != 0
		BEGIN
			ROLLBACK TRANSACTION
			RETURN
		END
	COMMIT TRANSACTION
END;
GO

-----------------------------------------------------Listar Banco-----------------------------------------------------
DROP PROCEDURE ListarBancos;
GO

CREATE PROCEDURE ListarBancos(@cantidad as integer,@omitir as integer)
AS
BEGIN
	DECLARE @sql varchar(255);

	SET @sql =
	'SELECT TOP ('+CONVERT(varchar, @cantidad)+') '+
	'id_banco, nombre_banco FROM Banco '+
	'WHERE id_banco NOT IN (SELECT TOP ('+CONVERT(varchar,@omitir)+') id_banco FROM Banco)';

	EXEC (@sql);
END;
GO

-----------------------------------------------------Contar Bancos-----------------------------------------------------
DROP PROCEDURE ContarBancos;
GO

CREATE PROCEDURE ContarBancos
AS
BEGIN
	SELECT COUNT(*) as num FROM Banco;
END;
GO

-----------------------------------------------------Agregar Cuenta-----------------------------------------------------
DROP PROCEDURE AgregarCuenta;
GO

CREATE PROCEDURE AgregarCuenta(@cuenta varchar(10),@banco numeric(11),@saldo_inicial numeric(16,2),@saldo_actual numeric(16,2))
AS
BEGIN
	BEGIN TRANSACTION
	INSERT INTO Cuenta_Banco(cuenta_banco,id_banco,saldo_inicial,saldo) VALUES(@cuenta,@banco,@saldo_inicial,@saldo_actual);
	IF @@ERROR != 0
		BEGIN
			ROLLBACK TRANSACTION
			RETURN
		END
	COMMIT TRANSACTION
END;
GO

-----------------------------------------------------Eliminar Cuenta-----------------------------------------------------
DROP PROCEDURE EliminarCuenta;
GO

CREATE PROCEDURE EliminarCuenta(@cuenta varchar(10))
AS
BEGIN
	BEGIN TRANSACTION
	DELETE FROM Cuenta_Banco WHERE cuenta_banco=@cuenta;
	IF @@ERROR != 0
		BEGIN
			ROLLBACK TRANSACTION
			RETURN
		END
	COMMIT TRANSACTION
END;
GO

-----------------------------------------------------Actualizar Cuenta-----------------------------------------------------
DROP PROCEDURE ActualizarCuenta;
GO

CREATE PROCEDURE ActualizarCuenta(@cuenta varchar(10),@banco numeric(11),@saldo_ini numeric(16,2),@saldo numeric(16,2))
AS
BEGIN
	BEGIN TRANSACTION
	UPDATE Cuenta_Banco SET id_banco=@banco,saldo_inicial=@saldo_ini,saldo=@saldo WHERE cuenta_banco=@cuenta;
	IF @@ERROR != 0
		BEGIN
			ROLLBACK TRANSACTION
			RETURN
		END
	COMMIT TRANSACTION
END;
GO

-----------------------------------------------------Listar Cuentas-----------------------------------------------------
DROP PROCEDURE ListarCuentasBanco;
GO

CREATE PROCEDURE ListarCuentasBanco(@cantidad as integer,@omitir as integer,@cuenta as varchar(10), @banco as numeric(11))
AS
BEGIN
	DECLARE @sql varchar(1000);
	DECLARE @where varchar(100);

	SET @where =
		(CASE WHEN (@cuenta IS NULL) THEN '' ELSE ' AND c.cuenta_banco LIKE '+dbo.EscapeStringForLike(@cuenta) END) +
		(CASE WHEN (@banco IS NULL) THEN '' ELSE ' AND c.id_banco='+dbo.EscapeString(@banco) END);

	SET @sql =
	'SELECT TOP ('+CONVERT(varchar, @cantidad)+') '+
		'c.cuenta_banco, c.id_banco, b.nombre_banco, c.saldo_inicial, c.saldo FROM Cuenta_Banco c '+
	'INNER JOIN Banco b ON (c.id_banco=b.id_banco) '+
	'WHERE c.cuenta_banco NOT IN ('+
		'SELECT TOP ('+CONVERT(varchar,@omitir)+') cuenta_banco FROM Cuenta_Banco c WHERE 0=0' + @where +
	')'+@where;

	EXEC (@sql);
END;
GO

-----------------------------------------------------Contar Cuentas-----------------------------------------------------
DROP PROCEDURE ContarCuentasBanco;
GO

CREATE PROCEDURE ContarCuentasBanco(@cuenta as varchar(10), @banco as numeric(11))
AS
BEGIN
	SELECT COUNT(*) as num FROM Cuenta_Banco
	WHERE cuenta_banco LIKE dbo.WhenNull(@cuenta, cuenta_banco, '%'+@cuenta+'%')
	AND id_banco=(CASE WHEN (@banco IS NULL) THEN id_banco ELSE dbo.EscapeString(@banco) END);
END;
GO
